USE MASTER
GO

BEGIN TRY
    -- kill all other connections
    ALTER DATABASE EasyTicket
    SET SINGLE_USER 
    WITH ROLLBACK IMMEDIATE;

    DROP DATABASE EasyTicket;
END TRY 
BEGIN CATCH
END CATCH
GO

CREATE DATABASE EasyTicket
GO

ALTER DATABASE EasyTicket
SET MULTI_USER 
GO

USE EasyTicket
GO


------------------------------------------------------
-- TABLE ----- M001_USER_PROFILE ------------------------
------------------------------------------------------
CREATE TABLE M001_USER_PROFILE
(
    UserId INT IDENTITY(1000, 1) PRIMARY KEY
    ,UserName NVARCHAR(100) NOT NULL UNIQUE
)
GO


------------------------------------------------------
-- TABLE ----- M051_WEB_MEMBERSHIP ----------------------
------------------------------------------------------
CREATE TABLE M051_WEB_MEMBERSHIP
(
    UserId INT NOT NULL
    ,CreateDate DATETIME NOT NULL DEFAULT GETDATE()
    ,ConfirmationToken NVARCHAR(128) NULL
    ,IsConfirmed BIT NULL DEFAULT 0
	,IsActived BIT NULL DEFAULT 1
	,IsDeleted BIT NULL DEFAULT 0
    ,LastPasswordFailureDate DATETIME NULL
    ,PasswordFailuresSinceLastSuccess INT NOT NULL DEFAULT 0
    ,Password NVARCHAR(128) NOT NULL
    ,PasswordChangedDate DATETIME NULL
    ,PasswordSalt NVARCHAR(128) NOT NULL
    ,PasswordVerificationToken NVARCHAR(128) NULL
    ,PasswordVerificationTokenExpirationDate DATETIME NULL
	,PRIMARY KEY (UserId)
)
GO

------------------------------------------------------
-- TABLE ----- M061_WEB_ROLES ---------------------------
------------------------------------------------------
CREATE TABLE M061_WEB_ROLES
(
    RoleId INT IDENTITY(1000, 1)
    ,RoleName NVARCHAR(256) NOT NULL
    ,RoleDescription NVARCHAR(MAX) NULL
	,PRIMARY KEY (RoleId)
	,UNIQUE (RoleName)
)
GO


------------------------------------------------------
-- TABLE ----- M062_WEB_USER_IN_ROLES ----------------
------------------------------------------------------
CREATE TABLE M062_WEB_USER_IN_ROLES
(
    UserId INT NOT NULL -- FOREIGN KEY REFERENCES M001_USER_PROFILE(UserId)
    ,RoleId INT NOT NULL -- FOREIGN KEY REFERENCES M061_WEB_ROLES(RoleId)
    ,PRIMARY KEY (UserId, RoleId)
)
GO

------------------------------------------------------
-- TABLE ----- C001_COUNTRY --------------------------
------------------------------------------------------
CREATE TABLE C001_COUNTRY
(
	CountryId INT IDENTITY(1000, 1)
	,CountryName NVARCHAR(100) NOT NULL 
	,PRIMARY KEY (CountryId)
	,UNIQUE (CountryName)
)
GO


------------------------------------------------------
-- TABLE ----- C002_CITY ---------------------------
------------------------------------------------------
CREATE TABLE C002_CITY
(
	CityId INT IDENTITY(1000, 1)
	,CountryId INT NOT NULL -- FOREIGN KEY REFERENCES C001_COUNTRY(CountryId)
	,CityName NVARCHAR(100) NOT NULL
	,PRIMARY KEY(CityId)
	,UNIQUE (CountryId, CityName)
)
GO

------------------------------------------------------
-- TABLE ----- P100_CINEMA ---------------------------
------------------------------------------------------
CREATE TABLE P100_CINEMA
(
	CinemaId INT IDENTITY(1000, 1)
    ,CinemaName NVARCHAR(200) NOT NULL
    ,CityId INT NOT NULL -- FOREIGN KEY REFERENCES C002_CITY(CityId)
    ,CinemaAddress NVARCHAR(200) NOT NULL
	,PRIMARY KEY(CinemaId)
	,UNIQUE (CinemaName, CityId, CinemaAddress)
)
GO

------------------------------------------------------
-- TABLE ----- P101_CINEMAROOM -----------------------
------------------------------------------------------
CREATE TABLE P101_CINEMAROOM
(
    RoomId INT IDENTITY(1000, 1)
    ,RoomName VARCHAR(10) NOT NULL
    ,CinemaId INT NOT NULL -- FOREIGN KEY REFERENCES P100_CINEMA(CinemaId)
	,PRIMARY KEY (RoomId)
	,UNIQUE (CinemaId, RoomName)
)
GO

------------------------------------------------------
-- TABLE ----- P102_CINEMAROOMSEAT -------------------
------------------------------------------------------
CREATE TABLE P102_CINEMAROOMSEAT
(
    SeatId INT IDENTITY(1000, 1)
    ,SeatName VARCHAR(10) NOT NULL
    ,RoomId INT NOT NULL -- FOREIGN KEY REFERENCES P101_CINEMAROOM(RoomId)
	,PRIMARY KEY (SeatId)
	,UNIQUE (RoomId, SeatName)
)
GO

------------------------------------------------------
-- TABLE ----- P103_CINEMATIMESLOT -------------------
------------------------------------------------------
CREATE TABLE P103_CINEMATIMESLOT
(
    SlotId INT IDENTITY(1000, 1)
    ,SlotName VARCHAR(10) NOT NULL
    ,StartTime TIME NOT NULL
    ,EndTime TIME NOT NULL
	,PRIMARY KEY (SlotId)
	,UNIQUE (SlotName)
	,UNIQUE (StartTime, EndTime)
)
GO

------------------------------------------------------
-- TABLE ----- M101_DIRECTOR ----------------------------
------------------------------------------------------
CREATE TABLE M101_DIRECTOR
(
    DirectorId INT IDENTITY(1000, 1)
    ,DirectorName VARCHAR(100) NOT NULL
    ,DirectorGender CHAR(1) NULL
    ,DirectorBirth DATETIME NULL
    ,DirectorNationality INT -- FOREIGN KEY REFERENCES C001_COUNTRY(CountryId)
    ,DirectorHomeCountry INT -- FOREIGN KEY REFERENCES C001_COUNTRY(CountryId)
	,PRIMARY KEY (DirectorId)
)
GO

------------------------------------------------------
-- TABLE ----- M102_ACTOR ----------------------------
------------------------------------------------------
CREATE TABLE M102_ACTOR
(
    ActorId INT IDENTITY(1000, 1) 
    ,ActorName VARCHAR(100) NOT NULL
    ,ActorGender CHAR(1) NULL
    ,ActorBirth DATETIME NULL
    ,ActorNationality INT -- FOREIGN KEY REFERENCES C001_COUNTRY(CountryId)
    ,ActorHomeCountry INT -- FOREIGN KEY REFERENCES C001_COUNTRY(CountryId)
	,PRIMARY KEY (ActorId)
)
GO

------------------------------------------------------
-- TABLE ----- E99_CATEGORY --------------------------
------------------------------------------------------
CREATE TABLE E99_CATEGORY
(
    CategoryId INT IDENTITY(1000, 1)
    ,CategoryGroup INT NOT NULL
    ,CategoryGroupDes NVARCHAR(100) NULL
    ,CategoryName NVARCHAR(20)
	,PRIMARY KEY (CategoryId)
    ,UNIQUE(CategoryGroup, CategoryName)
)
GO

------------------------------------------------------
-- TABLE ----- E100_MOVIE ----------------------------
------------------------------------------------------
CREATE TABLE E100_MOVIE
(
    MovieId INT IDENTITY(1000, 1)
    ,MovieName NVARCHAR(200) NOT NULL
	,MovieYear VARCHAR(4) NOT NULL
    ,MovieDescription NVARCHAR(MAX) NULL
	,PRIMARY KEY (MovieId)
)
GO

------------------------------------------------------
-- TABLE ----- E101_MOVIECATEGORY ----------------------------
------------------------------------------------------
CREATE TABLE E101_MOVIECATEGORY
(
    MovieId INT NOT NULL -- FOREIGN KEY REFERENCES E100_MOVIE(MovieId)
    ,CategoryId INT -- FOREIGN KEY REFERENCES E99_CATEGORY(CategoryId)
	,PRIMARY KEY (MovieId, CategoryId)
)
GO

------------------------------------------------------
-- TABLE ----- M111_MOVIEDIRECTOR --------------------
------------------------------------------------------
CREATE TABLE M111_MOVIEDIRECTOR
(
    DirectorId INT NOT NULL -- FOREIGN KEY REFERENCES M101_DIRECTOR(DirectorId)
    ,MovieId INT NOT NULL -- FOREIGN KEY REFERENCES E100_MOVIE(MovieId)
	,PRIMARY KEY (DirectorId, MovieId)
)
GO

------------------------------------------------------
-- TABLE ----- M112_ACTORMOVIE -------------------------
------------------------------------------------------
CREATE TABLE M112_ACTORMOVIE
(
    ActorId INT NOT NULL -- FOREIGN KEY REFERENCES M102_ACTOR(ActorId)
    ,MovieId INT NOT NULL -- FOREIGN KEY REFERENCES E100_MOVIE(MovieId)
	,PRIMARY KEY (ActorId, MovieId)
)
GO


------------------------------------------------------
-- TABLE ----- P111_MOVIERUN ----------------------
------------------------------------------------------
CREATE TABLE P111_MOVIERUN
(
    RunId INT IDENTITY(1000, 1) PRIMARY KEY
    ,MovieId INT NOT NULL -- FOREIGN KEY REFERENCES E100_MOVIE(MovieId)
    ,RoomId INT NOT NULL -- FOREIGN KEY REFERENCES P101_CINEMAROOM(RoomId)
    ,ShowDay DATE NOT NULL
    ,TimeSlot INT NOT NULL -- FOREIGN KEY REFERENCES P103_CINEMATIMESLOT(SlotId)
	,UNIQUE (MovieId, RoomId, ShowDay, TimeSlot)
)
GO

------------------------------------------------------
-- TABLE ----- P112_MOVIETICKET ----------------------
------------------------------------------------------
CREATE TABLE P112_MOVIETICKET
(
    TicketId INT IDENTITY(1000, 1)
    ,RunId INT NOT NULL -- FOREIGN KEY REFERENCES P111_MOVIERUN(RunId)
    ,SeatId INT NOT NULL -- FOREIGN KEY REFERENCES P102_CINEMAROOMSEAT(SeatId)
	,TicketPrice FLOAT NOT NULL
	,PRIMARY KEY (TicketId)
	,UNIQUE (RunId, SeatId)
)
GO

USE MASTER
GO